using System;
using System.IO;
using System.Collections;
using System.IO.Compression;
using System.Text;
using System.Collections.Generic;

namespace Jonathan.SharpDB
{
	/// <summary>
	/// Delegate for Notifying of a new Query Result.
	/// </summary>
	/// <param name="sender">Results Row</param>
	/// <param name="e">Event Args</param>
    public delegate void NewResultHandler(object sender, EventArgs e);

	/// <summary>
	/// SharpDB Main Database class.
	/// </summary>
    public class Database
    {
        #region - Variables -

        /// <summary>
        /// Collection of Tables...
        /// </summary>
        private TableCollection m_Tables;

        /// <summary>
        /// The file name of the database...
        /// </summary>
        private string m_FileName;

        /// <summary>
        /// The semi-colon delimited list of tables...
        /// </summary>
        private string m_TableList;

        /// <summary>
        /// Determines if changes to the database should be written Immediately...
        /// </summary>
        private bool m_WriteChanges;

        /// <summary>
        /// Array for the lengths of the tables...
        /// </summary>
        private int[] m_TblLengths;

        /// <summary>
        /// Length of Table Info at beginning of file...
        /// </summary>
        private int m_TblInfoLength;

        /// <summary>
        /// Event for a Returned Result...
        /// </summary>
        public event NewResultHandler NewResult;

        #endregion

        #region - Constructors -

        /// <summary>
        /// Creates a new SharpDB...
        /// </summary>
        public Database() : this(null)
        {
        }

        /// <summary>
        /// Creates a new SharpDB and opens the given database...
        /// </summary>
        /// <param name="filePath">Path to database.</param>
        public Database(string filePath)
        {
            this.NewResult += new NewResultHandler(Database_NewResult);
            this.m_WriteChanges = false;
            this.m_Tables = new TableCollection();
            this.m_TblLengths = new int[0];
            if (filePath != null)
            {
                this.OpenDatabase(filePath);
            }
        }

        #endregion

        #region - Events -

        internal void TriggerNewResult(object sender)
        {
            this.NewResult(sender, new EventArgs());
        }

        internal void Database_NewResult(object sender, EventArgs e)
        {
        }

        #endregion

        #region - Properties -

        /// <summary>
        /// Gets or Set if the database will write any changes Immediately, or wait until the <code>Commit()</code> method is called...
        /// </summary>
        public bool WriteChangesImmediately
        {
            get { return this.m_WriteChanges; }

            set { this.m_WriteChanges = value; }
        }

        /// <summary>
        /// Gets a semi-colon delimited list of the table names...
        /// </summary>
        public string TableList
        {
            get { return this.m_TableList; }
        }

		/// <summary>
		/// Gets the list of Columns for the given table.
		/// </summary>
		/// <param name="tableName">Name of Table.</param>
		/// <returns>List of Columns - List(Column)</returns>
		public List<Column> GetTableColumns (string tableName)
		{
			Table tbl = this.m_Tables[tableName];
			int tblIndex = this.m_Tables.IndexOf(tableName);
			if (tbl.Columns.Count <= 0)
			{
				byte[] tblBytes = this.GetCompressedTableBytes(tblIndex);
				MemoryStream msTbl = new MemoryStream(Compressor.Decompress(tblBytes));
				BinaryReader brTbl = new BinaryReader(msTbl);
				GetTableColumnsFromStream(brTbl, tbl);

				tbl.Status = TableStatus.DATA_NOT_READ;
			}
			return tbl.Columns;
        }

        /// <summary>
        /// Gets the Path to the Database...
        /// </summary>
        public string DBPath
        {
            get { return this.m_FileName; }
        }

        #endregion

        #region - Open Database -

        /// <summary>
        /// Opens a Database...
        /// </summary>
        /// <param name="filePath">Path to Database.</param>
        /// <returns>DBMessage</returns>
        public DBMessage OpenDatabase(string filePath)
        {
            // Save the file path...
            this.m_FileName = filePath;

            // If we have a database open, close it...
            CloseDatabase();

            // Check for db extension...
            if (Path.GetExtension(filePath).ToLower() != ".db")
            {
                return DBMessage.INVALID_DATABASE;
            }

            // Check to see if this is an existing database...
            bool existingFile = File.Exists(filePath);

			try
			{
				// Get a stream to the file...
				using (FileStream fs = this.GetFileAccess(filePath))
				{
					if (existingFile)
					{
						//GZipStream zs = new GZipStream(fs, CompressionMode.Decompress);
						BinaryReader br = new BinaryReader(fs);

						// Read the version...
						this.m_TblInfoLength = br.ReadInt32();
						// Get the list of tables...
						byte[] tblInfo = new byte[this.m_TblInfoLength];
						br.Read(tblInfo, 0, this.m_TblInfoLength);

						MemoryStream msTblInfo = new MemoryStream(Compressor.Decompress(tblInfo));
						BinaryReader brTblInfo = new BinaryReader(msTblInfo);
						this.m_TableList = brTblInfo.ReadString();
						// Add Tables...
						string[] tblsplit = this.m_TableList.Split(';');

						this.m_TblLengths = new int[tblsplit.Length];
						for (int a = 0; a < tblsplit.Length; a++)
						{
							this.m_Tables.Add(new Table(tblsplit[a], this));
							this.m_TblLengths[a] = brTblInfo.ReadInt32();
						}

						brTblInfo.Close();
						msTblInfo.Close();
						br.Close();
					}
					fs.Close();
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
            return DBMessage.SUCCESS;
        }

		/// <summary>
		/// Gets access to the database's file.
		/// </summary>
		/// <param name="path">Path to Database.</param>
		/// <returns>FileStream to Database File.</returns>
        private FileStream GetFileAccess (string path)
        {
            try
            {
                FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                return fs;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return this.GetFileAccess(path);
        }

        #endregion

        #region - Close Database -

        /// <summary>
        /// Closes the Database...
        /// </summary>
        public void CloseDatabase()
        {
            this.m_Tables = null;
            this.m_Tables = new TableCollection();
        }

        #endregion

        #region - Create Table -

        /// <summary>
        /// Adds a Table to the database...
        /// </summary>
        /// <param name="tableName">Name of Table to Add.</param>
        /// <returns>DBMessage</returns>
        public DBMessage CreateTable(string tableName)
        {
            return this.CreateTable(tableName, null);
        }

        /// <summary>
        /// Adds a Table to the database with the given columns...
        /// </summary>
        /// <param name="tableName">Name of Table to Add.</param>
        /// <param name="columns">Array of Column Names to Add.</param>
        /// <returns>DBMessage</returns>
        public DBMessage CreateTable(string tableName, string[] columns)
        {
            // Check for table...
            foreach (Table table in this.m_Tables)
            {
                if (table.Name.Equals(tableName, StringComparison.CurrentCultureIgnoreCase))
                {
                    return DBMessage.TABLE_EXISTS;
                }
            }

			// You cannot have these in your table name...
			if (tableName.Contains(";"))
			{
				return DBMessage.INVALID_TABLE_NAME;
			}

            // Add the new Table...
            this.m_Tables.Add(new Table(tableName, this));

            // Add the Columns...
            if (columns != null)
            {
                foreach (string col in columns)
                {
                    this.m_Tables[this.m_Tables.Count - 1].AddColumn(col);
                }
            }

            // Update the List of Tables...
            this.RedoTableList();
            // Update the List of Lengths...
            this.RedoLengthArray(-1);

            return DBMessage.SUCCESS;
        }

        #endregion

        #region - Add Column -

        /// <summary>
        /// Adds a Column to the given table...
        /// </summary>
        /// <param name="tableName">Name of Table to Add Column.</param>
        /// <param name="columnName">Name of Column to Add.</param>
        /// <param name="columnType">Type of Column to Add.</param>
        /// <returns>DBMessage</returns>
        public DBMessage AddColumn(string tableName, string columnName, ColumnType columnType)
        {
            foreach (Table table in this.m_Tables)
            {
                if (table.Name.Equals(tableName, StringComparison.CurrentCultureIgnoreCase))
                {
                    return table.AddColumn(columnName, columnType);
                }
            }

            return DBMessage.TABLE_DOES_NOT_EXIST;
        }

        #endregion

        #region - Insert -

        /// <summary>
        /// Inserts Data into a Table, this is fast, but not recommended because it does not cast data types for the columns...
        /// </summary>
        /// <param name="tableName">Name of table to insert data into.</param>
        /// <param name="row">ArrayList of Data.</param>
        public DBMessage Insert(string tableName, ArrayList row)
        {
            // Look for Table...
            int tblIndex = this.m_Tables.IndexOf(tableName);
            if (tblIndex == -1)
            {
                return DBMessage.TABLE_DOES_NOT_EXIST;
            }

            if (this.m_Tables[tblIndex].Status == TableStatus.DATA_NOT_READ)
            {
                // Get Data...
                this.GetTableData(tableName, null);
            }

            // Add Row...
            this.m_Tables[tblIndex].AddRow(row);

            if (this.m_WriteChanges)
            {
                // Write Data...
                this.WriteTableData(this.m_Tables[tblIndex]);
            }

            return DBMessage.SUCCESS;
        }

        /// <summary>
        /// Inserts Data into a Table...
        /// </summary>
        /// <param name="tableName">Name of table to insert data into.</param>
        /// <param name="columns">Arry of Column Names.</param>
        /// <param name="data">Array of Data.</param>
        private DBMessage Insert(Query query)
        {
            // Look for Table...
            int tblIndex = this.m_Tables.IndexOf(query.Table);
            if (tblIndex == -1)
            {
                return DBMessage.TABLE_DOES_NOT_EXIST;
            }

            if (this.m_Tables[tblIndex].Status == TableStatus.DATA_NOT_READ)
            {
                // Get Data...
                this.GetTableData(query.Table, null);
            }

            Table tbl = this.m_Tables[tblIndex];
            // Create Row..
            ArrayList row = new ArrayList();
            for (int a = 0; a < tbl.Columns.Count; a++)
            {
                bool added = false;
                Column c = tbl.Columns[a] as Column;
                switch (c.ColumnType)
                {
                    #region - String -
                    case ColumnType.STRING:
                        for (int b = 0; b < query.Columns.Count; b++)
                        {
                            if (((string)query.Columns[b]).Equals(c.ColumnName, StringComparison.CurrentCultureIgnoreCase))
                            {
                                row.Add(Convert.ToString(query.Values[b]));
                                added = true;
                                break;
                            }
                        }

                        
                        if(!added)
                        {
                            row.Add("");
                        }
                        break;
                    #endregion
                    #region - Int -
                    case ColumnType.INT:
                        for (int b = 0; b < query.Columns.Count; b++)
                        {
                            if (((string)query.Columns[b]).Equals(c.ColumnName, StringComparison.CurrentCultureIgnoreCase))
                            {
                                row.Add(Convert.ToInt32(query.Values[b]));
                                added = true;
                                break;
                            }
                        }
                        
                        if(!added)
                        {
                            row.Add(0);
                        }
                        break;
                    #endregion
                    #region - File -
                    case ColumnType.STREAM:
                        for (int b = 0; b < query.Columns.Count; b++)
                        {
                            if (((string)query.Columns[b]).Equals(c.ColumnName, StringComparison.CurrentCultureIgnoreCase))
                            {
                                row.Add(query.Values[b]);
                                added = true;
                                break;
                            }
                        }
                        
                        if(!added)
                        {
                            row.Add(0x0);
                        }
                        break;
                    #endregion
                    #region - Decimal -
                    case ColumnType.DECIMAL:
                        for (int b = 0; b < query.Columns.Count; b++)
                        {
                            if (((string)query.Columns[b]).Equals(c.ColumnName, StringComparison.CurrentCultureIgnoreCase))
                            {
                                row.Add(Convert.ToDecimal(query.Values[b]));
                                added = true;
                                break;
                            }
                        }
                        
                        if(!added)
                        {
                            row.Add(0.0D);
                        }
                        break;
                    #endregion
                    #region - DateTime -
                    case ColumnType.DATETIME:
                        for (int b = 0; b < query.Columns.Count; b++)
                        {
                            if (((string)query.Columns[b]).Equals(c.ColumnName, StringComparison.CurrentCultureIgnoreCase))
                            {
                                row.Add(DateTime.Parse((string)query.Values[b]));
                                added = true;
                                break;
                            }
                        }

                        if(!added)
                        {
                            row.Add(DateTime.Now);
                        }
                        break;
                    #endregion
                }
            }

            tbl.AddRow(row);

            query.SetRowsAffected(1);

            return DBMessage.SUCCESS;
        }

        #endregion

        #region - Select -

        /// <summary>
        /// Select items from the database...
        /// </summary>
        /// <param name="query">Query to run.</param>
        /// <returns>DBMessage</returns>
        private DBMessage Select(Query query)
        {
            // Look for Table...
            int tblIndex = this.m_Tables.IndexOf(query.Table);
            if (tblIndex == -1)
            {
                return DBMessage.TABLE_DOES_NOT_EXIST;
            }

            if (this.m_Tables[tblIndex].Status == TableStatus.DATA_NOT_READ)
            {
                // Get Data...
                this.GetTableData(query.Table, query);

                return DBMessage.SUCCESS;
            }
            else
            {
                Table tbl = this.m_Tables[tblIndex];

                return tbl.SelectRows(query);
            }
        }

        #endregion

        #region - Update -

		/// <summary>
		/// Updates Data in a Table...
		/// </summary>
        /// <param name="query">Query to Run.</param>
		/// <returns>DBMessage</returns>
        private DBMessage Update(Query query)
        {
            // Look for Table...
            int tblIndex = this.m_Tables.IndexOf(query.Table);
            if (tblIndex == -1)
            {
                return DBMessage.TABLE_DOES_NOT_EXIST;
            }

            if (this.m_Tables[tblIndex].Status == TableStatus.DATA_NOT_READ)
            {
                // Get Data...
                this.GetTableData(query.Table, null);
            }

            Table tbl = this.m_Tables[tblIndex];

            // Update rows...
            query.SetRowsAffected(tbl.UpdateRow(query));

            return DBMessage.SUCCESS;
        }

        #endregion

        #region - Delete -

        /// <summary>
        /// Delete Data from Table...
        /// </summary>
        /// <param name="tableName">Name of table to insert data into.</param>
        /// <param name="columns">Arry of Column Names.</param>
        /// <param name="operators">Arry of Operators.</param>
        /// <param name="data">Array of Data.</param>
        private DBMessage Delete(Query query)
        {
            // Look for Table...
            int tblIndex = this.m_Tables.IndexOf(query.Table);
            if (tblIndex == -1)
            {
                return DBMessage.TABLE_DOES_NOT_EXIST;
            }

            if (this.m_Tables[tblIndex].Status == TableStatus.DATA_NOT_READ)
            {
                // Get Data...
                this.GetTableData(query.Table, null);
            }

            Table tbl = this.m_Tables[tblIndex];

            // Remove rows...
            query.SetRowsAffected(tbl.RemoveRow(query));

            return DBMessage.SUCCESS;
        }

        #endregion

        #region - Run Query -

        /// <summary>
        /// Runs a given Query...
        /// </summary>
        /// <param name="query">Query to Run.</param>
        /// <returns>DBMessage</returns>
        public DBMessage RunQuery(Query query)
        {
            int a = 0;
            switch (query.QueryType)
            {
                #region - Drop -
                case QueryType.DROP:
                    return this.DropTable(query.Table);
                #endregion
                #region - Create -
                case QueryType.CREATE:
                    if (this.CreateTable(query.Table) == DBMessage.SUCCESS)
                    {
                        for (a = 0; a < query.Columns.Count; a++)
                        {
                            this.AddColumn(query.Table, (string)query.Columns[a], (ColumnType)query.Values[a]);
                        }

                        return DBMessage.SUCCESS;
                    }
                    else
                    {
                        return DBMessage.QUERY_ERROR;
                    }
                #endregion
                #region - Insert -
                case QueryType.INSERT:
                    if (query.Columns.Count == 0)
                    {
                        return DBMessage.NO_QUERY_DATA;
                    }

                    return this.Insert(query);
                #endregion
                #region - Update -
                case QueryType.UPDATE:
                    return this.Update(query);
                #endregion
                #region - Delete - 
                case QueryType.DELETE:
                    return this.Delete(query);
                #endregion
                #region - Select -
                case QueryType.SELECT:
                    return this.Select(query);
                #endregion
            }

            return DBMessage.UNKNOWN_QUERY;
        }

        #endregion

        #region - Redo Table List -

        /// <summary>
        /// Recreates the semi-colon delimited list of tables...
        /// </summary>
        private void RedoTableList()
        {
            this.m_TableList = "";
            for (int a = 0; a < this.m_Tables.Count; a++)
            {
                if (a != 0)
                {
                    this.m_TableList += ";";
                }

                this.m_TableList += this.m_Tables[a].Name;
            }
        }

        /// <summary>
        /// Recreates the list of table lengths...
        /// </summary>
        /// <param name="removeIndex">Index to remove, if -1, it will add and index.</param>
        private void RedoLengthArray(int removeIndex)
        {
            if (removeIndex == -1)
            {
                // Add a new index...
                int[] newList = new int[this.m_TblLengths.Length + 1];
                Array.Copy(this.m_TblLengths, newList, this.m_TblLengths.Length);
                this.m_TblLengths = newList;
            }
            else
            {
                // Remove an index...
                int[] newList = new int[this.m_TblLengths.Length - 1];
                int b = 0;
                for (int a = 0; a < newList.Length; a++)
                {
                    if (a == removeIndex)
                    {
                        b++;
                    }

                    newList[a] = this.m_TblLengths[b];
                    b++;
                }

                this.m_TblLengths = newList;
            }
        }

        #endregion

        #region - Drop Table -

        /// <summary>
        /// Removes a table from the database...
        /// </summary>
        /// <param name="tableName">Name of Table to Remove.</param>
        public DBMessage DropTable(string tableName)
        {
            foreach (Table table in this.m_Tables)
            {
                if (table.Name.Equals(tableName, StringComparison.CurrentCultureIgnoreCase))
                {
                    this.RedoLengthArray(this.m_Tables.IndexOf(table));
                    this.m_Tables.Remove(table);
                    this.RedoTableList();
                    if (this.m_WriteChanges)
                    {
                        this.WriteDatabase();
                    }
                    return DBMessage.SUCCESS;
                }
            }

            return DBMessage.TABLE_DOES_NOT_EXIST;
        }

        #endregion

        #region - Get Table Data -

        private int GetTableData(string tableName, Query query)
		{
			int tblIndex = this.m_Tables.IndexOf(tableName);
			if (tblIndex <= -1)
				return -1;

			byte[] tblBytes = this.GetCompressedTableBytes(tblIndex);

            MemoryStream msTbl = new MemoryStream(Compressor.Decompress(tblBytes));
            BinaryReader brTbl = new BinaryReader(msTbl);
			Table tbl = this.m_Tables[tblIndex];

			int cols = GetTableColumnsFromStream(brTbl, tbl);

			int a = 0;
            bool runQuery = false;
            ArrayList colList = new ArrayList();
            if (query != null)
            {
                Results r = new Results();
                runQuery = true;

                if (query.ModColumns.Count == 1)
                {
                    if (query.ModColumns[0].ToString() == "*")
                    {
                        for (int z = 0; z < tbl.Columns.Count; z++)
                        {
                            Column col = tbl.Columns[z];
                            colList.Add(col.ColumnName);
                            r.AddColumn(col.ColumnName, col.ColumnType);
                        }
                    }
                    else
                    {
                        for (int z = 0; z < query.ModColumns.Count; z++)
                        {
                            colList.Add(query.ModColumns[z]);
                            Column col = tbl.Columns[Common.GetColumnIndex(query.ModColumns[z].ToString(), tbl.Columns)];
                            r.AddColumn(col.ColumnName, col.ColumnType);
                        }
                    }
                }
                else
                {
                    for (int z = 0; z < query.ModColumns.Count; z++)
                    {
                        colList.Add(query.ModColumns[z]);
                        Column col = tbl.Columns[Common.GetColumnIndex(query.ModColumns[z].ToString(), tbl.Columns)];
                        r.AddColumn(col.ColumnName, col.ColumnType);
                    }
                }

                query.SetResults(r);
            }

            int rows = brTbl.ReadInt32();
            for (a = 0; a < rows; a++)
            {
                ArrayList items = new ArrayList();
                for (int b = 0; b < cols; b++)
                {
                    Column col = tbl.Columns[b] as Column;
                    switch (col.ColumnType)
                    {
                        case ColumnType.STRING:
                            items.Add(brTbl.ReadString());
                            break;
                        case ColumnType.INT:
                            items.Add(brTbl.ReadInt32());
                            break;
                        case ColumnType.DECIMAL:
                            items.Add(brTbl.ReadDecimal());
                            break;
                        case ColumnType.DATETIME:
                            items.Add(DateTime.Parse(brTbl.ReadString()));
                            break;
                        case ColumnType.STREAM:
                            int len = brTbl.ReadInt32();
                            if (len == 0)
                            {
                                items.Add(0x0);
                            }
                            else
                            {
                                byte[] buffer = new byte[len];
                                brTbl.Read(buffer, 0, buffer.Length);
                                MemoryStream ms = new MemoryStream(buffer);
                                items.Add(ms);
                            }
                            break;
                    }
                }

                if (runQuery)
                {
                    if (Common.CheckRowMatch(items, tbl.Columns, query.Columns, query.Operators, query.Values))
                    {
                        ArrayList row = new ArrayList();
                        for (int b = 0; b < colList.Count; b++)
                        {
                            int colIndex = Common.GetColumnIndex(colList[b].ToString(), tbl.Columns);
                            if (colIndex != -1)
                            {
                                row.Add(items[colIndex]);
                            }
                        }

                        NewResult(row, new EventArgs());
                        query.Results.AddRow(row);
                    }
                }
                tbl.AddRow(items);
            }

            if (runQuery)
            {
                // Sort Results...
                if (!string.IsNullOrEmpty(query.OrderByCol))
                {
                    query.Results.Sort(query.OrderByCol, query.OrderDir);
                }
            }

            this.m_Tables.SetTable(tblIndex, tbl);

            brTbl.Close();
            msTbl.Close();

            return tblIndex;
        }

		/// <summary>
		/// Gets the Columns for a table in the database's file.
		/// </summary>
		/// <param name="brTbl">BinaryReader to file.</param>
		/// <param name="tbl">Table to get columns for.</param>
		/// <returns>Number of Columns.</returns>
		private static int GetTableColumnsFromStream (BinaryReader brTbl, Table tbl)
		{
			// Read Table Name.
			tbl.Name = brTbl.ReadString();
			// Read Number of Columns.
			int cols = brTbl.ReadInt32();

			// Get Columns.
			for (int a = 0; a < cols; a++)
			{
				string colName = brTbl.ReadString();
				switch (brTbl.ReadString())
				{
					case "STRING":
						tbl.AddColumn(colName);
						break;
					case "INT":
						tbl.AddColumn(colName, ColumnType.INT);
						break;
					case "DECIMAL":
						tbl.AddColumn(colName, ColumnType.DECIMAL);
						break;
					case "DATETIME":
						tbl.AddColumn(colName, ColumnType.DATETIME);
						break;
					case "FILE":
						tbl.AddColumn(colName, ColumnType.STREAM);
						break;
				}
			}
			return cols;
		}

		/// <summary>
		/// Gets the bytes for the given table's index from the file.
		/// </summary>
		/// <param name="tblIndex">Index of Table to get.</param>
		/// <returns>Array of compressed bytes for given table index.</returns>
		private byte[] GetCompressedTableBytes (int tblIndex)
		{
			// Get a stream to the file...
			FileStream fs = this.GetFileAccess(this.m_FileName);// new FileStream(this.m_FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
			// Get the list of tables...
			BinaryReader br = new BinaryReader(fs);

			long startPos = this.GetTableStartPos(tblIndex);

			br.BaseStream.Seek(startPos, SeekOrigin.Begin);
			byte[] tblBytes = new byte[this.m_TblLengths[tblIndex]];
			br.Read(tblBytes, 0, tblBytes.Length);

			fs.Close();
			br.Close();
			return tblBytes;
		}

        #endregion

        #region - Write Table Data -

        private void WriteTableData(Table table)
        {
            int tblIndex = this.m_Tables.IndexOf(table);

            MemoryStream msTable = new MemoryStream();
            BinaryWriter bwTable = new BinaryWriter(msTable);

            // Write Table Name...
            bwTable.Write(table.Name);

            // Write Columns...
            bwTable.Write(table.Columns.Count);
            int a = 0;
            for (a = 0; a < table.Columns.Count; a++)
            {
                Column col = table.Columns[a] as Column;
                bwTable.Write(col.ColumnName);
                bwTable.Write(col.ColumnType.ToString());
            }

            // Write Rows...
            bwTable.Write(table.Rows.Count);
            for (a = 0; a < table.Rows.Count; a++)
            {
                ArrayList row = table.Rows[a] as ArrayList;
                for (int c = 0; c < table.Columns.Count; c++)
                {
                    bwTable.Write(row[c].ToString());
                }
            }

            bwTable.Flush();
            // Compress Table...
            byte[] bytes = Compressor.Compress(msTable.ToArray());

            // Close Table Streams...
            bwTable.Close();
            msTable.Close();

            if (bytes.Length == this.m_TblLengths[tblIndex])
            {
                // Just Write the new table...
                FileStream fs = new FileStream(this.m_FileName, FileMode.Open, FileAccess.ReadWrite);
                BinaryWriter bw = new BinaryWriter(fs);
                bw.Seek((int)this.GetTableStartPos(tblIndex), SeekOrigin.Begin);

                bw.Write(bytes);

                bw.Close();
                fs.Close();
            }
            else
            {
                FileStream fs = new FileStream(this.m_FileName, FileMode.Open, FileAccess.ReadWrite);
                BinaryReader br = new BinaryReader(fs);

                // Get Old data...
                byte[] beforeBuffer = new byte[this.GetTableStartPos(tblIndex) - this.GetTableStartPos(0)];
                byte[] afterBuffer = new byte[this.FileLength() - (this.GetTableStartPos(tblIndex) + this.m_TblLengths[tblIndex])];
                if (beforeBuffer.Length > 0)
                {
                    br.BaseStream.Seek(this.GetTableStartPos(0), SeekOrigin.Begin);
                    br.Read(beforeBuffer, 0, beforeBuffer.Length);
                }

                if (afterBuffer.Length > 0)
                {
                    br.BaseStream.Seek(this.GetTableStartPos(tblIndex) + this.m_TblLengths[tblIndex], SeekOrigin.Begin);
                    br.Read(afterBuffer, 0, afterBuffer.Length);
                }

                // Close streams...
                br.Close();
                fs.Close();
                fs = null;

                string tempFileName = Path.GetRandomFileName();
                fs = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                BinaryWriter bw = new BinaryWriter(fs);

                // Write Table info...
                MemoryStream msTblInfo = new MemoryStream();
                BinaryWriter bwTblInfo = new BinaryWriter(msTblInfo);
                bwTblInfo.Write(this.m_TableList);
                this.m_TblLengths[tblIndex] = bytes.Length;
                for (int b = 0; b < this.m_TblLengths.Length; b++)
                {
                    bwTblInfo.Write(this.m_TblLengths[b]);
                }
                byte[] tblInfoBytes = Compressor.Compress(msTblInfo.ToArray());
                bw.Write(tblInfoBytes.Length);
                bw.Write(tblInfoBytes);

                // Write Tables before Table...
                if (beforeBuffer.Length > 0)
                {
                    bw.Write(beforeBuffer);
                }

                // Write Table...
                bw.Write(bytes);

                // Write Tables After Table...
                if (afterBuffer.Length > 0)
                {
                    bw.Write(afterBuffer);
                }

                // Close streams...
                bw.Close();
                fs.Close();

                File.Delete(this.m_FileName);
                File.Copy(tempFileName, this.m_FileName);
                File.Delete(tempFileName);
            }

            table.Status = TableStatus.DATA_OK;
        }

        #endregion

        #region - Byte Positions -

        /// <summary>
        /// Gets the byte index to start reading from...
        /// </summary>
        /// <param name="index">Index of table in the table list.</param>
        /// <returns>byte index</returns>
        private long GetTableStartPos(int index)
        {
            long startPos = (long)this.m_TblInfoLength + sizeof(int);
            for (int a = 0; a < index; a++)
            {
                startPos += this.m_TblLengths[a];
            }

            return startPos;
        }

        /// <summary>
        /// Gets the Total Length of the file...
        /// </summary>
        /// <returns>Total Length of File.</returns>
        private int FileLength()
        {
            int startPos = this.m_TblInfoLength + sizeof(int);
            for (int a = 0; a < this.m_TblLengths.Length; a++)
            {
                startPos += this.m_TblLengths[a];
            }

            return startPos;
        }

        #endregion

        #region - Commit -

        /// <summary>
        /// Writes all of the Database Info to the disk...
        /// </summary>
        public void Commit()
        {
            this.WriteDatabase();
        }

        /// <summary>
        /// Writes the given table to the database...
        /// </summary>
        /// <param name="tableName">Name of Table.</param>
        public void Commit(string tableName)
        {
            this.WriteTableData(this.m_Tables[tableName]);
        }

        #endregion

        #region - Write Database -

        /// <summary>
        /// Re-write the entire database...
        /// </summary>
        private void WriteDatabase()
        {
            // Make sure all of the Data is read...
            for(int z = 0; z < this.m_Tables.Count; z++)
            {
                Table tbl = this.m_Tables[z];
                if (tbl.Status == TableStatus.DATA_NOT_READ)
                {
                    this.GetTableData(tbl.Name, null);
                }
            }

            string tempFileName = Path.GetRandomFileName();
            FileStream fs = new FileStream(tempFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            BinaryWriter bw = new BinaryWriter(fs);

            // Stream and Writer for Table Information...
            MemoryStream msTableInfo = new MemoryStream();
            BinaryWriter bwTableInfo = new BinaryWriter(msTableInfo);
            bwTableInfo.Write(this.m_TableList);

            try
            {
                ArrayList comList = new ArrayList();

                // Write Version Information...
                //bw.Write("SharpDB - Version: " + this.m_Version);
                //bw.Write(this.m_TableList);

                foreach (Table table in this.m_Tables)
                {
                    MemoryStream msTable = new MemoryStream();
                    BinaryWriter bwTable = new BinaryWriter(msTable);

                    // Write Table Name...
                    bwTable.Write(table.Name);

                    // Write Columns...
                    bwTable.Write(table.Columns.Count);
                    int a = 0;
                    for (a = 0; a < table.Columns.Count; a++)
                    {
                        Column col = table.Columns[a] as Column;
                        bwTable.Write(col.ColumnName);
                        bwTable.Write(col.ColumnType.ToString());
                    }

                    // Write Rows...
                    bwTable.Write(table.Rows.Count);
                    for (a = 0; a < table.Rows.Count; a++)
                    {
                        ArrayList row = table.Rows[a] as ArrayList;
                        for (int c = 0; c < table.Columns.Count; c++)
                        {
                            Column col = table.Columns[c] as Column;
                            switch (col.ColumnType)
                            {
                                case ColumnType.STRING:
                                    bwTable.Write(row[c].ToString());
                                    break;
                                case ColumnType.INT:
                                    bwTable.Write(Convert.ToInt32(row[c]));
                                    break;
                                case ColumnType.DECIMAL:
                                    bwTable.Write(Convert.ToDecimal(row[c]));
                                    break;
                                case ColumnType.DATETIME:
                                    bwTable.Write(Convert.ToDateTime(row[c]).ToString());
                                    break;
                                case ColumnType.STREAM:
                                    Stream stream = row[c] as Stream;
                                    if (stream == null)
                                    {
                                        bwTable.Write(0);
                                    }
                                    else
                                    {
                                        bwTable.Write(stream.Length);
                                        byte[] buffer = new byte[stream.Length];
                                        stream.Read(buffer, 0, buffer.Length);
                                        bwTable.Write(buffer);
                                    }
                                    break;
                            }
                        }
                    }

                    bwTable.Flush();
                    // Compress Table...
                    byte [] bytes = Compressor.Compress(msTable.ToArray());
                    // Write length of Table...
                    bwTableInfo.Write(bytes.Length);
                    comList.Add(bytes);

                    // Close Table Streams...
                    bwTable.Close();
                    msTable.Close();

                    // Write Table to Memory Stream...
                    //bwCompressed.Write(bytes, 0, bytes.Length);
                    table.Status = TableStatus.DATA_READ;
                }

                bwTableInfo.Flush();
                // Compress Table Info...
                byte[] tblInfo = Compressor.Compress(msTableInfo.ToArray());
                // Write Table Info Length...
                bw.Write(tblInfo.Length);
                // Write Table Info...
                bw.Write(tblInfo, 0, tblInfo.Length);
                // Write Compressed Tables...
                //bw.Write(msCompressed.ToArray(), 0, (int)msCompressed.Length);
                for (int a = 0; a < comList.Count; a++)
                {
                    byte[] bts = (byte[])comList[a];
                    bw.Write(bts, 0, bts.Length);
                }
            }
            catch (System.IO.IOException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                bwTableInfo.Close();
                msTableInfo.Close();
                bw.Flush();
                bw.Close();
                fs.Close();
            }

            File.Delete(this.m_FileName);
            File.Copy(tempFileName, this.m_FileName);
            File.Delete(tempFileName);
        }

        #endregion

        /// <summary>
        /// Checks if the Database has a table by the given name...
        /// </summary>
        /// <param name="tableName">Name of Table to Check for.</param>
        /// <returns>True if table exists; otherwise false.</returns>
        public bool ContainsTable (string tableName)
        {
            string[] tbls = this.m_TableList.Split(';');
            foreach (string table in tbls)
            {
                if (table.ToLower().Equals(tableName.ToLower()))
                    return true;
            }

            return false;
        }
    }
}
